import serial
import pymysql
import datetime


# 串口读取的类
class ComThread:
    # 类初始化,port默认/dev/ttyUSB0 buand默认9600
    def __init__(self, port="/dev/ttyUSB0", buand=9600):
        self.com = serial.Serial(port, buand)
        if not self.com.isOpen():
            self.com.open()

    def com_open(self):
        if not self.com.isOpen():
            self.com.open()

    def com_close(self):
        self.com.close()

    def read_data(self):
        self.data = self.com.read(12)
        return self.data


# 数据库操作的类
class MysqlHelper(object):
    """"""
    def __init__(self, host, db, user, password, port=3306, charset="utf8"):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password
        self.charset = charset
        self.connection = None
        self.cursor = None

    def open(self):
        self.connection = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            passwd=self.password,
            db=self.db,
            charset=self.charset)
        self.cursor = self.connection.cursor()

    def close(self):
        self.cursor.close()
        self.connection.close()

    # 增删改
    def cud(self, sql, params):
        try:
            self.open()
            self.cursor.execute(sql, params)
            self.connection.commit()
            self.close()
        except Exception as message:
            print("增删改异常信息为：%s" % message)

    # 查询
    def inquire(self, sql, params):
        try:
            self.open()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchall()
            self.close()
            return result
        except Exception as message:
            print("查询异常信息为：%s" % message)


# 获取用户输入
def get_data():
    print("请刷卡,然后录入信息...")
    key = res.read_data()
    print("卡的键值:")
    print(key)
    name = input("姓名：")
    gender = input("性别:")
    num = input("学号:")
    major = input("专业班级:")
    phone = input("手机号:")
    return (key, name, gender, num, major, phone)


# 打印查询信息
def print_m(self):
    print("当前时间:%s-%s-%s" % (datetime.datetime.now().hour,datetime.datetime.now().min,datetime.datetime.now().second))
    print("*" * 30)
    print("姓名：%s" % self[2])
    print("性别：%s" % self[3])
    print("学号：%s" % self[4])
    print("专业：%s" % self[5])
    print("电话：%s" % self[6])
    print("*" * 30)


# 添加信息
def adder(key, name, gender, num, major, phone):
    sql = "insert into group_member(card_key,name,gender,num,major,phone) values(%s,%s,%s,%s,%s,%s)"
    mysql = MysqlHelper("192.168.21.130", "python", "python", "python")
    mysql.cud(sql, (key, name, gender, num, major, phone))


# 修改信息
def revise(key, name, gender, num, major, phone):
    sql="update group_member set name=%s,gender=%s,num=%s,major=%s,phone=%s where card_key=%s"
    # sql = "insert into group_member(card_key,name,gender,num,major,phone) values(%s,%s,%s,%s,%s,%s)"
    mysql = MysqlHelper("192.168.21.130", "python", "python", "python")
    mysql.cud(sql, (name, gender, num, major, phone, key))


# 查询信息
def inquire():
    print("请刷卡查询信息...")
    key = res.read_data()
    # 查询语句
    sql = "select * from group_member where card_key=%s"
    mysql = MysqlHelper("192.168.21.130", "python", "python", "python")
    result = mysql.inquire(sql, (key))
    return result


res = ComThread()
res.com_open()
while True:
    key_num = input("\033[0;31m%s\033[0m" % "选择功能:1.添加信息,2.查询信息(数字):")
    if "1" == key_num:
        result = inquire()
        if len(result) > 0:
            key_k = input("\033[0;31m%s\033[0m" % "该卡已经存在是否修改(y/n):")
            if ("y" == key_k)or("Y" == key_k):
                data = get_data()
                revise(data[0], data[1], data[2], data[3], data[4], data[5])
            elif ("n" == key_k)or("N" == key_k):
                continue
        elif len(result) == 0:
            data = get_data()
            adder(data[0], data[1], data[2], data[3], data[4], data[5])
    elif "2" == key_num:
        da = inquire()
        if len(da) > 0:
            print_m(da[0])
        else:
            print("没有该卡信息!")
    else:
        print("选择错误,请重新选择!")

res.com_close()